package com.qf.dao.impl;

import com.qf.dao.StudentDao;
import com.qf.pojo.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by 54110 on 2021/7/5.
 */
public class StudentDaoImpl implements StudentDao {

    private static Connection conn=null;

    public StudentDaoImpl(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/xa_2104?useUnicode=true&characterEncoding=utf-8", "root", "59852369");

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(e.getMessage());
        }
    }


    @Override
    public List<Student> findAll() {
        String sql = "select * from tb_student";
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            preparedStatement  = conn.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            List list = new ArrayList();
            while (resultSet.next()){
                Student student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setAddress(resultSet.getString("address"));
                student.setStudentName(resultSet.getString("student_name"));
                student.setSex(resultSet.getString("sex"));
                student.setAge(resultSet.getInt("age"));
                list.add(student);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println(e.getMessage());
        }finally {
            try {
                resultSet.close();
                preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    @Override
    public Integer insertStudent(Student student) {

        String sql = "insert into tb_student(student_name,age,sex,address) values(?,?,?,?)";
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setObject(1,student.getStudentName());
            preparedStatement.setObject(2,student.getAge());
            preparedStatement.setObject(3,student.getSex());
            preparedStatement.setObject(4,student.getAddress());

            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return 0;
    }

    @Override
    public Integer deleteById(Integer id) {

        String sql ="delete from tb_student where id =?";

        PreparedStatement prepareStatement = null;

        try {
            prepareStatement = conn.prepareStatement(sql);
            prepareStatement.setObject(1,id);
            int i = prepareStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                prepareStatement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return 0;
    }

    @Override
    public Student findById(Integer id) {
     String sql ="select * from tb_student where id = ?";
        PreparedStatement preparestatement=null;
        ResultSet resultSet=null;
        try {
            preparestatement = conn.prepareStatement(sql);
            preparestatement.setObject(1,id);
           resultSet = preparestatement.executeQuery();
            Student student = new Student();
            while (resultSet.next()){
                student.setId(resultSet.getInt("id"));
                student.setAddress(resultSet.getString("address"));
                student.setStudentName(resultSet.getString("student_name"));
                student.setSex(resultSet.getString("sex"));
                student.setAge(resultSet.getInt("age"));
            }
            return student;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                resultSet.close();
                preparestatement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return null;
    }

    @Override
    public Integer updateById(Student student) {

        String sql = "update tb_student set student_name=?,address=?,age=?,sex=? where id = ?";
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setObject(1,student.getStudentName());
            preparedStatement.setObject(3,student.getAge());
            preparedStatement.setObject(4,student.getSex());
            preparedStatement.setObject(2,student.getAddress());
            preparedStatement.setObject(5,student.getId());

            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return 0;
    }
}
